package com.zrrd._01_jdbc;

import org.junit.Test;

import java.sql.*;

/**
 * @author WanJl
 * @version 1.0
 * @title TestQueryMySQL
 * @description
 * @create 2024/1/11 15:22
 */
public class TestQueryMySQL {
    /**
     * 查询
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Test
    public void query() throws SQLException, ClassNotFoundException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.创建连接
        String url="jdbc:mysql://127.0.0.1:3306/jms2301_db01";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3.创建Statement对象
        Statement stat = conn.createStatement();
        //4.编写SQL语句
        String sql="SELECT * FROM jms2301_db01.student";
        //5.执行SQL语句，返回执行结果
        // 执行executeQuery方法，是用来查询记录的，所以要返回一个结果集(ResultSet)。
        ResultSet rs = stat.executeQuery(sql);
        //6.处理执行结果
        while (rs.next()){
            int id=rs.getInt(1);
            String name=rs.getString(2);
            int age=rs.getInt(3);
            String sex=rs.getString(4);
            String address=rs.getString(5);
            double math= rs.getDouble(6);
            double english= rs.getDouble(7);
            String hire_date=rs.getString(8);
            System.out.println(id+","+name+","+age+","+sex+","+address+","+math+","+english+","+hire_date);
            System.out.println("-----------------------------");
        }
        //7.释放资源
        rs.close();
        stat.close();
        conn.close();
    }

    /**
     * 插入
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void insert() throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.创建连接
        String url="jdbc:mysql://127.0.0.1:3306/jms2301_db01";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3.创建Statement对象
        Statement stat = conn.createStatement();
        //4.编写SQL语句
        String sql= "INSERT INTO jms2301_db01.student (id, name, age, sex, address, math, english, hire_date) " +
                    "VALUES (10,'刘邦',25,'男','沛县',70,20,'2024-1-11')";
        //5.执行SQL语句，返回执行结果
        //如果是添加数据记录，就需要执行executeUpdate方法，更新数据记录，返回的是更新的行数。
        int count = stat.executeUpdate(sql);
        //6.处理执行结果
        if (count>0){
            System.out.println("插入成功....");
        }else {
            System.out.println("插入失败....");
        }
    }

    /**
     * 修改
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void update() throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.创建连接
        String url="jdbc:mysql://127.0.0.1:3306/jms2301_db01";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3.创建Statement对象
        Statement stat = conn.createStatement();
        //4.编写SQL语句
        String sql= "UPDATE jms2301_db01.student SET name='项羽' WHERE id=10";
        //5.执行SQL语句，返回执行结果
        //如果是添加数据记录，就需要执行executeUpdate方法，更新数据记录，返回的是更新的行数。
        int count = stat.executeUpdate(sql);
        //6.处理执行结果
        if (count>0){
            System.out.println("修改成功....");
        }else {
            System.out.println("修改失败....");
        }
    }
    /**
     * 修改
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void delete() throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.创建连接
        String url="jdbc:mysql://127.0.0.1:3306/jms2301_db01";
        String username="root";
        String password="123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //3.创建Statement对象
        Statement stat = conn.createStatement();
        //4.编写SQL语句
        String sql= "DELETE FROM jms2301_db01.student  WHERE id=10";
        //5.执行SQL语句，返回执行结果
        //如果是添加数据记录，就需要执行executeUpdate方法，更新数据记录，返回的是更新的行数。
        int count = stat.executeUpdate(sql);
        //6.处理执行结果
        if (count>0){
            System.out.println("删除成功....");
        }else {
            System.out.println("删除失败....");
        }
    }
}
